package com.wxd.utils;

import com.wxd.modules.function.entity.EmpEntity;

import java.sql.*;
import java.util.List;

public class DbConnectUtil {

    public static R connect(String ip, String dbType, String name, String pwd, List<EmpEntity> empEntityList) {
        Connection conn = null;
        Statement stmt = null;
        //注册驱动
        try {
            Driver driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
            //获取连接
            //假设每个站段都有一个test数据库
            String url = "jdbc:"+dbType+"://" + ip + ":3306/test?autoReconnect=true&useUnicode=true&createDatabaseIfNotExist=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai";
            String user = name;
            String password = pwd;
            conn = DriverManager.getConnection(url, user, password);//到了这里我们可以先执行编一下看是否连接到数据库
            //获取数据库的操作对象
            stmt = conn.createStatement();
            /**
             * 如果有这个表：
             * （1）没有这个数据时，直接插入操作
             * （2）有这个数据时，表示更新操作
             * 没有这个表，先建表。执行（1）,（2）
             */
            String createBrandDatabase =
                    "CREATE TABLE `emp_share`  (\n" +
                    "  `emp_id` bigint NOT NULL,\n" +
                    "  `emp_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                    "  `dept_id` bigint NULL DEFAULT NULL,\n" +
                    "  `gender` tinyint NULL DEFAULT NULL,\n" +
                    "  `birth_date` datetime NULL DEFAULT NULL,\n" +
                    "  `native_place` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `education` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `major` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `graduate_school` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `political_background` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `join_work_time` datetime NULL DEFAULT NULL,\n" +
                    "  `join_dept_time` datetime NULL DEFAULT NULL,\n" +
                    "  `mobile` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `addr` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `status` tinyint NULL DEFAULT NULL COMMENT '状态  0：在岗   1：死亡 2：退休 3：离职  4：调出',\n" +
                    "  `remarks` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `join_type` tinyint NULL DEFAULT NULL COMMENT '新进类型 0：新招人员 1：从其他单位调入',\n" +
                    "  `office` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职名（职务）',\n" +
                    "  `employ_mode` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `id_card` bigint NULL DEFAULT NULL,\n" +
                    "  `medical_id` bigint NULL DEFAULT NULL,\n" +
                    "  `archives_id` bigint NULL DEFAULT NULL,\n" +
                    "  `work_id` bigint NULL DEFAULT NULL,\n" +
                    "  `society_safeguard_id` bigint NULL DEFAULT NULL,\n" +
                    "  `skill_grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `skill_kid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `skill_pass_date` datetime NULL DEFAULT NULL,\n" +
                    "  `contract_start_date` datetime NULL DEFAULT NULL,\n" +
                    "  `contract_end_date` datetime NULL DEFAULT NULL,\n" +
                    "  `contract_relieve_date` datetime NULL DEFAULT NULL,\n" +
                    "  `is_solider` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `is_leader` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `is_internship` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `nationality` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,\n" +
                    "  `age` int NULL DEFAULT NULL,\n" +
                    "  PRIMARY KEY (`emp_id`) USING BTREE\n" +
                    ") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;";
            String checkTable="show tables like \"emp_share\"";
            ResultSet resultSet=stmt.executeQuery(checkTable);
            /**
             * 执行Sql语句
             */
            for(EmpEntity emp:empEntityList) {
                /*查询emp是否存在*/
                try {
                    ResultSet rs = stmt.executeQuery("select emp_name from emp_share where emp_id=" + emp.getEmpId());
                    {
                        if (rs.first()) {
                            System.out.println(emp.getEmpName() + "已存在");
                            //如果已存在，则更新
                            String sqlUpdate = "UPDATE emp_share" +
                                    " SET emp_id=" + emp.getEmpId() + ", emp_name='" + emp.getEmpName() + "',  dept_id=" + emp.getDeptId() + ",  gender=" + emp.getGender() + ", id_card=" + emp.getIdCard() +
                                    " WHERE emp_id=" + emp.getEmpId() + ";";
                            stmt.executeUpdate(sqlUpdate);
                        } else {
                            System.out.println(emp.getEmpName() + "不存在");
                            // 如果不存在则插入
                            String sqlInsert = "INSERT INTO `emp_share` (`emp_id`, `emp_name`, `dept_id`, `gender`, `id_card`)" +
                                    " VALUES (" + emp.getEmpId() + ", '" + emp.getEmpName() + "',  " + emp.getDeptId() + ",  " + emp.getGender() + ",  " + emp.getIdCard() + ");";
                            stmt.executeUpdate(sqlInsert);
                        }
                    }
                }
                catch (Exception e)
                {
                    return R.error("保存失败，错误提示:"+e.toString());
                }
            }

        }
        catch (Exception e)
        {
            return R.error("保存失败,请检查连接信息");
        }
        finally {
            /* 释放资源 （就是数据库连接使用结束后要关闭） */
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return R.ok();
    }
}
